package com.asistencia.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.asistencia.dao.interfaces.ProfessorDao;
import com.asistencia.domain.Career;
import com.asistencia.domain.Professor;



@Repository
@SuppressWarnings({ "rawtypes", "unchecked" })
public class ProfessorJdbc implements ProfessorDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Professor login(String username, String password)
			throws DataAccessException {
		String SQL ="SELECT idprofessor,username, name, lastname,mother_lastname,email, idcarreer FROM professor WHERE username= ? AND password = ? LIMIT 1";
		
		List matches=jdbcTemplate.query(SQL, new Object[]{username, password }, new RowMapper(){
		
		@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Professor pro = new Professor();
			Career car = new Career();
			
			pro.setIdProfessor(rs.getInt("idprofessor"));
			pro.setName(rs.getString("name"));
			pro.setLastname(rs.getString("lastname"));
			pro.setMotherLastname(rs.getString("mother_lastname"));
			pro.setUsername(rs.getString("username"));
			pro.setEmail(rs.getString("email"));
			car.setIdCareer(rs.getInt("idcarreer"));
			pro.setMyCareer(car);
					
			return pro;

		}});
		return (matches.size() > 0 ? (Professor) matches.get(0) : null);}
	

	@Override
	public Professor getById(Integer idProfessor) throws DataAccessException {
		String SQL="SELECT * FROM professor WHERE idprofessor = ?";
		List matches=jdbcTemplate.query(SQL, new Object[]{idProfessor}, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Professor pro = new Professor();
			Career car = new Career();
			
			pro.setIdProfessor(rs.getInt("idprofessor"));
			pro.setName(rs.getString("name"));
			pro.setLastname(rs.getString("lastname"));
			pro.setMotherLastname(rs.getString("mother_lastname"));
			pro.setUsername(rs.getString("username"));
			pro.setEmail(rs.getString("email"));
			car.setIdCareer(rs.getInt("idcarreer"));
			pro.setMyCareer(car);
					
			return pro;

		}});
		return (matches.size() > 0 ? (Professor) matches.get(0) : null);}
	

	@Override
	public List<Professor> listAll() throws DataAccessException {
		String SQL= "SELECT * FROM professor ";
		List matches=jdbcTemplate.query(SQL, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Professor pro = new Professor();
			Career car = new Career();
			
			pro.setIdProfessor(rs.getInt("idprofessor"));
			pro.setName(rs.getString("name"));
			pro.setLastname(rs.getString("lastname"));
			pro.setMotherLastname(rs.getString("mother_lastname"));
			pro.setUsername(rs.getString("username"));
			pro.setEmail(rs.getString("email"));
			car.setIdCareer(rs.getInt("idcarreer"));
			pro.setMyCareer(car);
					
			return pro;
			}
		});
		
		return (matches.size() > 0 ? (List<Professor>) matches : null);
	}

	@Override
	public List<Professor> listByName(String name) throws DataAccessException {
		String SQL ="SELECT * FROM professor WHERE name LIKE ? OR lastname LIKE ? ";
		List matches = jdbcTemplate.query(SQL,new Object []{"%"+name+"%", "%" +name+"%"}, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Professor pro = new Professor();
			Career car = new Career();
			
			pro.setIdProfessor(rs.getInt("idprofessor"));
			pro.setName(rs.getString("name"));
			pro.setLastname(rs.getString("lastname"));
			pro.setMotherLastname(rs.getString("mother_lastname"));
			pro.setUsername(rs.getString("username"));
			pro.setEmail(rs.getString("email"));
			car.setIdCareer(rs.getInt("idcarreer"));
					
			return pro;
			}
		});
		
		return (matches.size() > 0 ? (List<Professor>) matches : null);
	}

	@Override
	public List<Professor> listByCareer(Integer idCareer)
			throws DataAccessException {
		String SQL="SELECT * FROM professor  WHERE idcarreer =?";
		List matches=jdbcTemplate.query(SQL,new Object []{idCareer}, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Professor pro = new Professor();
			Career car = new Career();
			
			pro.setIdProfessor(rs.getInt("idprofessor"));
			pro.setName(rs.getString("name"));
			pro.setLastname(rs.getString("lastname"));
			pro.setMotherLastname(rs.getString("mother_lastname"));
			pro.setUsername(rs.getString("username"));
			pro.setEmail(rs.getString("email"));
			car.setIdCareer(rs.getInt("idcarreer"));
					
			return pro;
			}
		});
		
		return (matches.size() > 0 ? (List<Professor>) matches : null);
	}}
